import json
import pickle
import time
from idlelib import statusbar

from io import BytesIO
import random

from urllib.parse import quote

from django.core.cache import caches
from django.db.models import Q
from django.db.transaction import get_connection
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render, redirect

from django.utils import timezone
from django.utils.decorators import method_decorator
from django.views.decorators.cache import cache_page
from django_redis import get_redis_connection
from openpyxl import Workbook
from rest_framework.decorators import api_view
from rest_framework.response import Response
from rest_framework.viewsets import ModelViewSet, ReadOnlyModelViewSet

from CarQueryApp.models import Car, Record
from CarQueryApp.serializers import SearchSerializer
from CarQueryApp.utils import gen_md5_hex


@cache_page(timeout=1800, cache='default')
def index(request):
    return redirect('/static/html/carIndex.html')


# def search(request):
#
#     carno = request.POST.get('carno', '')
#     # 避免一加N查询
#     queryset = Record.objects.filter(is_delete=False).defer('is_delete', 'deleted_time', 'update_time').select_related(
#         'car').order_by('-makedate')
#     if carno:
#         # i 忽略大小写 contains两边模糊查询
#         queryset = queryset.filter(Q(car__carno__icontains=carno) | Q(car__owner__icontains=carno))
#
#
#     return render(request, 'carIndex.html', context=context)

@api_view(('GET',))  # 限制视图函数支持哪些请求方法
def get_record(request):
    '''FBV获取数据'''

    carno = request.GET.get('carno', '')
    print(carno)
    queryset = Record.objects.defer('is_delete', 'deleted_time', 'update_time').filter(
        Q(car__carno__icontains=carno) | Q(car__owner__icontains=carno))

    # 序列化对象，是一个集合需要写上many=True
    serializer = SearchSerializer(queryset, many=True)
    return Response(serializer.data)  # 框架给的response


# @method_decorator(decorator=cache_page(timeout=300,cache='default'),name='list')
# @method_decorator(decorator=cache_page(timeout=1800,cache='default'),name='retrieve')
class SearchViewSet(ModelViewSet):
    '''
    CBV获取数据
    '''
    queryset = Record.objects.filter(is_delete=False).defer('is_delete', 'deleted_time', 'update_time').select_related(
        'car').order_by('-makedate')
    serializer_class = SearchSerializer

    def list(self, request, *args, **kwargs):
        '''

        自定义编程式缓存

        :param request:
        :param args:
        :param kwargs:
        :return:
        '''
        # 设置键
        path = gen_md5_hex(request.get_full_path())
        key = f'qFCarAp:carapp:views:{path}'
        data = caches['default'].get(key)
        print(key)

        if data:
            resp = Response(json.loads(data))
        else:
            resp = super().list(request, *args, **kwargs)
            caches['default'].set(key, json.dumps(resp.data), timeout=300)
        return resp

    def get_queryset(self):
        queryset = self.queryset
        keyword = self.request.GET.get('keyword')
        print(keyword)
        if keyword:
            queryset = self.queryset.filter(
                Q(car__carno__icontains=keyword) | Q
                (car__owner__icontains=keyword))
        return queryset


def clear_cache(key_prefix):
    '''
    清理缓存
    :param key_prefix:
    :return:
    '''
    redis_cli = get_redis_connection()
    for key in redis_cli.keys(f'{key_prefix}*'):
        redis_cli.delete(key)


@api_view(('DELETE',))
def delet_record(request, rno):
    '''删除记录'''

    result = {
        'code': 10001,
        'msg': '操作失败'
    }
    if rno:
        record = Record.objects.filter(id=rno).first()
        if record and record.dealt:
            clear_cache('qFCarAp:carapp:views:')
            record.is_delete = True
            record.deleted_time = timezone.now()

            record.save()
            time.sleep(random.random() / 100000)
            clear_cache('qFCarAp:carapp:views:')

    return Response(status=204)


@api_view(('PUT',))
def handle_record(request, rno):
    '''受理函数'''
    result = {
        'code': 10001,
        'msg': '操作失败'
    }
    if rno:
        record = Record.objects.filter(id=rno).first()
        if record and not record.dealt:
            clear_cache('qFCarAp:carapp:views:')
            record.dealt = True
            record.updated_time = timezone.now()
            record.save()
            time.sleep(random.random() / 100000)
            clear_cache('qFCarAp:carapp:views:')

    return Response(status=200)


def export_excel(request):
    '''下载excle'''
    carno = request.GET.get('carno', '')
    # 避免一加N查询
    queryset = Record.objects.filter(is_delete=False).defer('is_delete', 'deleted_time', 'update_time').select_related(
        'car').order_by('-makedate')
    if carno:
        # i 忽略大小写 contains两边模糊查询
        queryset = queryset.filter(Q(car__carno__icontains=carno) | Q(car__owner__icontains=carno))

    wb = Workbook()
    sheet = wb.active
    col_names = ('车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '受理情况')
    for index, col in enumerate('ABCDEF'):
        sheet[f'{col}1'] = col_names[index]
    row = 2
    for record in queryset:
        sheet[f'A{row}'] = record.car.id
        sheet[f'B{row}'] = record.car.owner
        sheet[f'C{row}'] = record.reason
        sheet[f'D{row}'] = record.makedate
        sheet[f'E{row}'] = record.punish
        sheet[f'F{row}'] = '已受理' if record.dealt else '未受理'
        row += 1
    # BytesIO可变字节串
    buffer = BytesIO()

    wb.save(buffer)
    # 变成不变字节串
    resp = HttpResponse(buffer.getvalue(),
                        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

    filename = quote('违章记录表.xlsx')
    # inline 直接打开,一般不用
    resp['content-disposition'] = f'attachment; filename ="{filename}"'
    return resp


def get_bar_data(request):
    '''
    echarts获取数据
    :param request:
    :return:
    '''

    conn = get_connection()
    data = {}
    data1 = []
    with conn.cursor() as cursor:
        cursor.execute('select year(makedate) as ry, count(id) as total from tb_record group by ry')
        for row in cursor.fetchall():
            data['year'] = row[0]
            data['count'] = row[1]
            data1.append(data)
            data = {}
    context = {
        'data': data1,
        'status': 200
    }
    return JsonResponse(context)
